/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseserver;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import objects.FileInfo;
import objects.UserInfo;

/**
 *
 * @author tinvukhac
 */
public class DatabaseImpl extends UnicastRemoteObject implements databaseinterface.DatabaseInterface {

    private DatabaseServerUI serverUI;

    public DatabaseImpl() throws RemoteException {
    }

    public DatabaseImpl(DatabaseServerUI serverUI) throws RemoteException {
        this.serverUI = serverUI;
    }
    
    public static void main(String args[]) throws RemoteException{
        DatabaseImpl d = new DatabaseImpl();
        boolean b = d.IsFullTextInstalled();
    }

    /*
     * Function to get search query string with 2 mode params : String pattern,
     * boolean isFullTextSearch isFullTextSearch = false : use LIKE,
     * isFullTextSearch = true: use Full Text Search output : return query
     * String
     */
    public String getSearchQuery(String pattern, boolean isFullTextSearch) {
        if (isFullTextSearch) {
            return "SELECT * FROM files WHERE FREETEXT((fileName, fileDescription, userId), '" + pattern + "')";
        } else {
            return "SELECT * FROM files WHERE fileName LIKE '%" + pattern
                    + "%' OR fileDescription LIKE '%" + pattern + "%' OR userId LIKE '%" + pattern + "%'";
        }
    }

    public boolean IsFullTextInstalled() {
        //JOptionPane.showMessageDialog(null, "chay toi day");
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();
            String sqlQuery = "select SERVERPROPERTY('IsFullTextInstalled')";
            
            statement.executeUpdate(sqlQuery);
            
            JOptionPane.showMessageDialog(null, "chay toi day 2");
//            while(result.next()){
//                if(result.getInt("isInstalled") == 1){
//                    return true;
//                }
//                else{
//                    return false;
//                }
//            }
            return false;
        } catch (Exception e) {
            return false;
        }
    }

    // Implement the DatabaseInterface
    public String insertNewUser(String userId, String password, String ipAddress, int port) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();
            String sqlQuery = "INSERT INTO users(userId, password, userAddress, available,lastRequest,port) VALUES('" + userId + "', '" + password + "', '" + ipAddress + "', 1,getdate()," + port + ")";
            statement.executeUpdate(sqlQuery);
            conn.close();

            return "OK";
        } catch (Exception e) {
            return "SQL Exception : " + e.toString();
        }
    }

    public String checkLoginUser(String userId, String password, String ipAddress, int port) {
        try {
            String respond = "ok";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();
            String sqlQuery = "SELECT * FROM users WHERE userId = '" + userId + "' AND password = '" + password + "' AND available = 0";
            ResultSet rs = statement.executeQuery(sqlQuery);

            if (rs.next()) {
                // User is available, update ipAddress
                String updateQuery = "UPDATE users SET userAddress = '" + ipAddress + "', available = 1,lastRequest=getdate(),port=" + port + " WHERE userId = '" + userId + "' ";
                statement.executeUpdate(updateQuery);
            } else {
                // User is unavailable
                String sqlQuery1 = "SELECT * FROM users WHERE userId = '" + userId + "' AND password = '" + password + "' AND available = 1";
                ResultSet rs1 = statement.executeQuery(sqlQuery1);
                if (rs1.next()) {
                    respond = "account is loged in";
                } else {
                    respond = "username or password is incorrect";
                }
            }

            conn.close();
            return respond;

        } catch (Exception e) {
            return "SQL Exception : " + e.toString();
        }
    }

    public int insertNewFileInfo(String userId, String fileName, float fileSize, String fileDescription) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "INSERT INTO files(userId, fileName, fileSize, fileDescription) VALUES('"
                    + userId + "', '" + fileName + "', '" + fileSize + "', '" + fileDescription + "')"
                    + " INSERT INTO seeders(fileId, userId,isDownloading)"
                    + "SELECT TOP 1 fileId,'" + userId + "',0 FROM files ORDER BY fileId DESC";
            statement.executeUpdate(sqlQuery);

            String sqlQuery2 = "select MAX(fileId) as Id from files ";
            ResultSet rs = statement.executeQuery(sqlQuery2);

            int Id = 0;
            rs.next();
            Id = rs.getInt("Id");

            conn.close();

            return Id;
        } catch (Exception e) {
            return -1;
        }
    }

    public List<FileInfo> getSharedFileList() {
        List<FileInfo> list = new ArrayList<FileInfo>();

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "SELECT * FROM files";
            ResultSet rs = statement.executeQuery(sqlQuery);
            while (rs.next()) {
                list.add(new FileInfo(rs.getInt("fileId"), rs.getString("userId"),
                        rs.getString("fileName"), rs.getFloat("fileSize"),
                        rs.getString("FileDescription")));
            }

            conn.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error", "Error", 0);
        }

        return list;
    }

    public List<UserInfo> getFileSeeders(int fileId) {
        List<UserInfo> list = new ArrayList<UserInfo>();

        try {
            boolean available = false;

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();
            String sqlQuery = "SELECT userId FROM seeders WHERE fileId = '" + fileId + "'";
            ResultSet rs = statement.executeQuery(sqlQuery);
            while (rs.next()) {
                Statement statement2 = conn.createStatement();
                String sqlQuery2 = "SELECT * FROM users WHERE userId = '" + rs.getString("userId") + "'";
                ResultSet user = statement2.executeQuery(sqlQuery2);
                if (user.next()) {
                    if (user.getInt("available") == 1) {
                        list.add(new UserInfo(user.getString("userId"), user.getString("userAddress"), available, user.getInt("port")));
                    }
                }
            }
            conn.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString(), "Error", 0);
        }

        return list;
    }

    public FileInfo getFileInfo(int Id) throws RemoteException {
        FileInfo file = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "SELECT * FROM files WHERE fileId = " + Id;
            ResultSet rs = statement.executeQuery(sqlQuery);
            rs.next();
            file = new FileInfo(rs.getInt("fileId"), rs.getString("userId"),
                    rs.getString("fileName"), rs.getFloat("fileSize"),
                    rs.getString("FileDescription"));
            conn.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error", "Error", 0);
        }
        return file;
    }

    public void confirm(String userId) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "update users set available = 1, lastRequest = GETDATE()where userId ='" + userId + "'";
            statement.executeUpdate(sqlQuery);
            conn.close();
        } catch (Exception e) {
            System.out.println("Confirm in DatabaseImpl");
            e.printStackTrace();
        }
    }

    public int getNumberSharing(int fileId) {
        int number = 0;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "select COUNT(seeders.userId)as number from seeders, users "
                    + "where seeders.userId=users.userId AND users.available=1 AND seeders.fileId ="
                    + fileId + " AND seeders.isDownloading=0";
            ResultSet rs = statement.executeQuery(sqlQuery);
            rs.next();
            number = rs.getInt("number");
            conn.close();
        } catch (Exception e) {
            System.out.println("getNumberSharing in DatabaseImpl");
            e.printStackTrace();
        }
        return number;
    }

    public int getNumberDownloading(int fileId) {
        int number = 0;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "select COUNT(seeders.userId)as number from seeders, users "
                    + "where seeders.userId=users.userId AND users.available=1 AND seeders.fileId ="
                    + fileId + " AND seeders.isDownloading=1";
            ResultSet rs = statement.executeQuery(sqlQuery);
            rs.next();
            number = rs.getInt("number");
            conn.close();
        } catch (Exception e) {
            System.out.println("getNumberDownloading in DatabaseImpl");
            e.printStackTrace();
        }
        return number;
    }

    public List<UserInfo> getSharingUser(int fileId) {
        List<UserInfo> sharingList = new ArrayList<UserInfo>();
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "select users.* from users,seeders where users.userId=seeders.userId "
                    + " AND users.available=1 AND seeders.fileId="
                    + fileId + " AND seeders.isDownloading=0";
            ResultSet rs = statement.executeQuery(sqlQuery);
            while (rs.next()) {
                String userName = rs.getString("userId");
                String ip = rs.getString("userAddress");
                int available = rs.getInt("available");
                boolean avai = false;
                if (available == 1) {
                    avai = true;
                }
                int port = rs.getInt("port");
                UserInfo tempUserInfo = new UserInfo(userName, ip, avai, port);
                sharingList.add(tempUserInfo);
            }
            conn.close();
        } catch (Exception e) {
            System.out.println("getSharingUser in DatabaseImpl");
            e.printStackTrace();
        }
        return sharingList;
    }

    public List<UserInfo> getDownloadingUser(int fileId) {
        List<UserInfo> downloadingList = new ArrayList<UserInfo>();
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "select users.* from users,seeders where users.userId=seeders.userId "
                    + " AND users.available=1 AND seeders.fileId="
                    + fileId + " AND seeders.isDownloading=1";
            ResultSet rs = statement.executeQuery(sqlQuery);
            while (rs.next()) {
                String userName = rs.getString("userId");
                String ip = rs.getString("userAddress");
                int available = rs.getInt("available");
                boolean avai = false;
                if (available == 1) {
                    avai = true;
                }
                int port = rs.getInt("port");
                UserInfo tempUserInfo = new UserInfo(userName, ip, avai, port);
                downloadingList.add(tempUserInfo);
            }
            conn.close();

        } catch (Exception e) {
            System.out.println("getDownloadingUser in DatabaseImpl");
            e.printStackTrace();
        }
        return downloadingList;
    }

    public void confirmDownloading(int fileId, String userId) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "insert into seeders(fileId,userId,isDownloading)"
                    + " values (" + fileId + ",'" + userId + "',1)";
            statement.executeUpdate(sqlQuery);
        } catch (Exception e) {
            System.out.println("confirmDownloading in DatabaseImpl");
            e.printStackTrace();
        }
    }

    public void confirmDownloadComplete(int fileId, String userid) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = "update seeders set isDownloading=0 where fileId="
                    + fileId + " AND userId='" + userid + "'";
            statement.executeUpdate(sqlQuery);
        } catch (Exception e) {
            System.out.println("confirmDownloadComplete in DatabaseImpl");
            e.printStackTrace();
        }
    }

    public List<FileInfo> searchFile(String pattern, boolean isFullTextSearch) {
        List<FileInfo> list = new ArrayList<FileInfo>();
        isFullTextSearch = false;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                    + "databaseName=sharing;userName=sa;password=123456";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement statement = conn.createStatement();

            String sqlQuery = getSearchQuery(pattern, isFullTextSearch);
            ResultSet rs = statement.executeQuery(sqlQuery);
            while (rs.next()) {
                list.add(new FileInfo(rs.getInt("fileId"), rs.getString("userId"),
                        rs.getString("fileName"), rs.getFloat("fileSize"),
                        rs.getString("FileDescription")));
            }

            conn.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString(), "Error", 0);
        }

        return list;
    }
}
